package banco;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Collection;

import utilidades.FormataNumero;
import utilidades.FormatarData;
import dominio.Cliente;
import dominio.Historico;
import dominio.Valores;
import fachada.FachadaHistorico;

public class HistoricoDAO implements FachadaHistorico {

public void insertHistorico(Historico t, Cliente c, Valores v) throws ClassNotFoundException, IOException {
        
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;           
        
        try {
            conn = Banco.getConexaoEXTRATOPAES();
            stmt = conn.prepareStatement("insert into historico values (?, ?, ?, ?, ?, ?, ?)");
            stmt.setFloat(1, t.getTjlpMensal());
            stmt.setFloat(2, t.getAmortizacao());
            stmt.setFloat(3, t.getSaldoDevedor());
            stmt.setString(4, FormatarData.formatar(t.getMes()));
            stmt.setString(5, c.getCnpj().trim());
            stmt.setInt(6, v.getId());
            stmt.setFloat(7, t.getSaldoReal());
            stmt.executeUpdate();
        }
        catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        catch (Exception e) {
        	e.printStackTrace();
        }
        finally {
	        Banco.closeConexao(conn, rs, stmt);
        }
    }

@SuppressWarnings("finally")
public Collection selectHistorico(Cliente c, Valores v) throws ClassNotFoundException, IOException {
    
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    Collection<Historico> res = new ArrayList<Historico>();
    
    try {
        conn = Banco.getConexaoEXTRATOPAES();
        stmt = conn.prepareStatement("select * from historico where cnpj_cliente = ? and id_valores = ?");
        stmt.setString(1, c.getCnpj().trim());
        stmt.setInt(2, v.getId());
        rs = stmt.executeQuery();
        while (rs.next()){
            Historico h = new Historico();
            h.setAmortizacao(rs.getFloat("amortizacao"));
            h.setMes(FormatarData.inserirData(rs.getString("mes")));
            h.setSaldoDevedor(FormataNumero.formatarSaida(rs.getFloat("saldo_devedor")));
            h.setSaldoReal(FormataNumero.formatarSaida(rs.getFloat("saldo_real")));
            h.setTjlpMensal(FormataNumero.formatarSaida(rs.getFloat("tjlp_mensal")));
            h.setData_string(FormatarData.getMesAno(rs.getString("mes")));
            h.setId_historico(rs.getInt("id"));
            res.add(h);
        }
    }
    catch (SQLException ex) {
        ex.printStackTrace();
    }
    finally {
        Banco.closeConexao(conn, rs, stmt);
        return res;
    }
}

@SuppressWarnings("finally")
public Historico selectHistoricoById(int id) throws ClassNotFoundException, IOException {
    
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    Historico h = new Historico();
    
    try {
        conn = Banco.getConexaoEXTRATOPAES();
        stmt = conn.prepareStatement("select * from historico where id = ?");
        stmt.setInt(1, id);
        rs = stmt.executeQuery();
        while (rs.next()){
            h.setAmortizacao(rs.getFloat("amortizacao"));
            h.setMes(FormatarData.inserirData(rs.getString("mes")));
            h.setSaldoDevedor(FormataNumero.formatarSaida(rs.getFloat("saldo_devedor")));
            h.setSaldoReal(FormataNumero.formatarSaida(rs.getFloat("saldo_real")));
            h.setTjlpMensal(FormataNumero.formatarSaida(rs.getFloat("tjlp_mensal")));
            h.setData_string(FormatarData.getMesAno(rs.getString("mes")));
            h.setId_historico(rs.getInt("id"));
        }
    }
    catch (SQLException ex) {
        ex.printStackTrace();
    }
    finally {
        Banco.closeConexao(conn, rs, stmt);
        return h;
    }
}

public void updateHistorico(Historico h) throws ClassNotFoundException, IOException {
    
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    
    try {
        conn = Banco.getConexaoEXTRATOPAES();
        stmt = conn.prepareStatement("update historico set amortizacao = ?, tjlp_mensal = ?, saldo_devedor = ?, saldo_real = ?" +
        		"  where historico.id = ?");
        stmt.setFloat(1, h.getAmortizacao());        
        stmt.setFloat(2, h.getTjlpMensal());
        stmt.setFloat(3, h.getSaldoDevedor());
        stmt.setFloat(4, h.getSaldoReal());
        stmt.setInt(5, h.getId_historico());
        stmt.executeUpdate();
    }
    catch (SQLException ex) {
        ex.printStackTrace();
    }
    
    catch (Exception e) {
    	e.printStackTrace();
    }
    finally {
        Banco.closeConexao(conn, rs, stmt);
    }
}

}
